package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import modelo_relacional.Obra;
import modelo_relacional.Obra_Item;
import dao_framework.InsertUpdateDeleteDaoException;
import dao_framework.daoHelper;
import dao_framework.dbConn;

public class ObraItemDao {

	private final dbConn connDB;
	private final daoHelper helper;
	private final String dmlInsert;
	private final String dmlDelete;
	private final String dmlUpdate;
	private final String dmlDeleteObra;
	
	public ObraItemDao() {
		// TODO Auto-generated constructor stub
		connDB = new dbConn();
		helper = new daoHelper();
		dmlInsert = "insert into Obra_Item (Obra_ID, Carro_id, Descricao_Mate, Ref_Material) values (?,?,?,?)";
		dmlDelete = "delete from obra_item where obra_item_id = ?";
		dmlDeleteObra = "delete from obra_item where obra_id = ?";
		dmlUpdate = "update obra_item set Descricao_Mate = ?, Ref_Material = ?, Data_Ult_Alteracao = ? "
				+ "where obra_item_id = ?";
	}
	
	public void dmlObraItem(Obra obra, Obra_Item obraItem, String dml) {
		Connection conn = null;
		int obraItemID = 0;
		
		try {
			connDB.beginTransaction();
			conn = connDB.getConnectionFromContext();
			
			String timeStamp = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(Calendar.getInstance().getTime());
			
			if (dml.equals("Insert")) {
				obraItemID = helper.insertDelUpdReturnGenKey(conn, dmlInsert, Integer.toString(obra.getObra_id()),Integer.toString(obra.getCarro_id()),
						obraItem.getDesc_material(),obraItem.getRef_material());
				obraItem.setObra_item_id(obraItemID);
			}
			else if (dml.equals("Delete")) {
				helper.insertDelUpd(conn, dmlDelete, Integer.toString(obraItem.getObra_item_id()));
			}
			else if (dml.equals("DeleteObra")) {
				helper.insertDelUpd(conn, dmlDeleteObra, Integer.toString(obra.getObra_id()));
			}
			else if (dml.equals("Update")) {
				helper.insertDelUpd(conn, dmlUpdate, obraItem.getDesc_material(),obraItem.getRef_material(),timeStamp, Integer.toString(obraItem.getObra_item_id()));
			}
			connDB.endTransaction();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			connDB.rollbackTransaction();
			throw new InsertUpdateDeleteDaoException("Nao foi possivel realizar a transacao "+obraItem,e);
		}
		finally {
			connDB.close(conn);
		}
		
	}
	
	public List<Obra_Item> returnObraItemData (Obra obra) {
		Connection conn = null;
		PreparedStatement stm = null;
		ResultSet resultset = null;
		String dmlSelect = "select * from obra_item where obra_id = ? order by obra_item_id asc";
		List<Obra_Item> obrasItem = new ArrayList<Obra_Item>();
		
		try {
			connDB.beginTransaction();
			conn = connDB.getConnectionFromContext();
			
			obrasItem = setList(helper.getDados(conn,dmlSelect,obra.getObra_id()));
			
			connDB.endTransaction();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			connDB.closeAll(stm, conn);
			connDB.close(resultset);
		}
		
		return obrasItem;
	}
	
	public List<Obra_Item> setList (ResultSet rset) {
		List<Obra_Item> obrasItem = new ArrayList<Obra_Item>();
		
		try {
			while (rset.next()) {
				Obra_Item obraItem = new Obra_Item();
				obraItem.setObra_item_id(rset.getInt("obra_item_id"));;
				obraItem.setCarro_id(rset.getInt("carro_id"));
				obraItem.setObra_id(rset.getInt("obra_id"));
				obraItem.setDesc_material(rset.getString("Descricao_Mate"));
				obraItem.setRef_material(rset.getString("ref_material"));
				obraItem.setData_criacao(rset.getString("Data_criacao"));
				obrasItem.add(obraItem);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			connDB.close(rset);
		}
		
		return obrasItem;
	}
}
